package Application.Dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import DomainData.Reservation;
import Reservation.MysqlConnection;

public class ReservationDAO {
	static Connection connection = null;
	static ResultSet rs = null;
	SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

	public static int getTable(Date from, Date to, int numberOfPeople) {
		int tableID = -1;
		Timestamp fromTimestamp = new Timestamp(from.getTime());
		Timestamp toTimestamp = new Timestamp(to.getTime());

		Statement statement = null;
		String query = "SELECT `tableID` FROM `fkou`.`table` WHERE tableSize>="
				+ numberOfPeople + " AND NOT EXISTS"
				+ "(SELECT `tableID` FROM `fkou`.`reservation` "
				+ "WHERE(( `from`<='" + fromTimestamp + "' AND `to`>='"
				+ fromTimestamp + "')" + "OR(`from`<='" + toTimestamp
				+ "' AND `to`>='" + toTimestamp + "'))"
				+ " AND `table`.tableID = `reservation`.TableID) order by tableSize asc";
		 System.out.println("Query:"+query);
		try {
			connection = MysqlConnection.getConnection();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		if (connection == null) {
			System.out.println("********Connection is null");
		}

		try {
			statement = connection.createStatement();
			rs = statement.executeQuery(query);
			boolean more = rs.next();
			if (!more) {
				System.out.println("Sorry, there is no table available for the given time!");

			} else if (more) {
				tableID = rs.getInt("tableID");
				System.out.println("Table " + tableID + " is assigned");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return tableID;
	}

	public static int insertReservation(Reservation reservation) {
		int reservationID = -1;

		try {
			// before Insert user data, get connection with database
			connection = MysqlConnection.getConnection();
			// try to write Insert query for preparing
			if (connection != null) {
				java.sql.PreparedStatement stat = connection
						.prepareStatement("INSERT INTO `fkou`.reservation (`from`, `to`, numberOfPeople,tableID,customerID) "+ "VALUES(?,?,?,?,?)");

				stat.setTimestamp(1, new Timestamp(reservation.getFrom()
						.getTime()));
				stat.setTimestamp(2, new Timestamp(reservation.getTo()
						.getTime()));
				stat.setInt(3, reservation.getNumberOfPeople());
				stat.setInt(4, reservation.getTableID());
				stat.setInt(5, reservation.getCustomerID());
				stat.executeUpdate();

				System.out.println("Inserting Success");

				Statement statement = connection.createStatement();
				ResultSet rs = statement
						.executeQuery("select LAST_INSERT_ID()");
				if (rs.next()) {
					reservationID = rs.getInt("last_insert_id()");
				}

				reservation.setReservationID(reservationID);
				return reservationID;

			}
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println(e.toString());
		} finally {
			try {
				connection.close();
			} catch (Exception ee) {
			}
			;
		}

		return reservationID;
	}
	public static int modifyReservation(int reservationID, Date fromTime,Date toTime, int numberofpeople, Reservation r){
		int newTableID = -1;
		newTableID = getTable(fromTime,toTime,numberofpeople);
		System.out.println("New Avaiable table is "+newTableID);
		String modifySQL = "UPDATE reservation SET from=?, to=?, numberOfPeople=?, tableID=? WHERE reservationID=?";
		String deleteSQL = "DELETE FROM reservation"+" WHERE reservationID="+reservationID;
		
		try {
			// before Insert user data, get connection with database
			connection = MysqlConnection.getConnection();
			// try to write Insert query for preparing
			if (connection != null && newTableID>0) {
				PreparedStatement deleteStatement = connection.prepareStatement(deleteSQL);
				deleteStatement.executeUpdate();
				System.out.println("Successful in Removing old one");
				
				java.sql.PreparedStatement stat = connection
						.prepareStatement("INSERT INTO `fkou`.reservation (`reservationID`,`from`, `to`, numberOfPeople,tableID,customerID) "
								+ "VALUES(?,?,?,?,?,?)");
				
				stat.setInt(1, reservationID);
				stat.setTimestamp(2, new Timestamp(r.getFrom()
						.getTime()));
				stat.setTimestamp(3, new Timestamp(r.getTo()
						.getTime()));
				stat.setInt(4, r.getNumberOfPeople());
				stat.setInt(5, newTableID);
				stat.setInt(6, r.getCustomerID());
				stat.executeUpdate();
				
				
				
//				java.sql.PreparedStatement stat = connection.prepareStatement(modifySQL);

//				stat.setTimestamp(1, new Timestamp(fromTime.getTime()));
//				stat.setTimestamp(2, new Timestamp(toTime.getTime()));
//				stat.setInt(3, numberofpeople);
//				stat.setInt(4, newTableID);
//				stat.setInt(5, reservationID);
//				stat.executeUpdate();

				System.out.println("Update Success");
				
//				

				
				return newTableID;

			}
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println(e.toString());
		} finally {
			try {
				connection.close();
			} catch (Exception ee) {
			}
			;
		}
		
		
		
		return newTableID;
	}
	
	public static int removeReservation(int reservationID){
		int id = reservationID;
		String sql = "DELETE FROM reservation"+" WHERE reservationID="+id;
		try {
			// before Insert user data, get connection with database
			connection = MysqlConnection.getConnection();
			// try to write Insert query for preparing
			if (connection != null) {
				System.out.println(sql);
				java.sql.PreparedStatement stat = connection.prepareStatement(sql);
				stat.executeUpdate();
				System.out.println("Successful in Remove");
				return id;

			}
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println(e.toString());
		} finally {
			try {
				connection.close();
			} catch (Exception ee) {
			}
			;
		}
		
		return id;
	}
	public static List<Reservation> getAvailable(int customerID){
		List<Reservation> reservationsList = new ArrayList<Reservation>();
		SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
		Date currentdate = new Date();
		
		try {
			connection = MysqlConnection.getConnection();
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		Statement statement = null;
		String sql = "SELECT * FROM reservation WHERE customerID="+customerID+" and `to` > CURRENT_TIMESTAMP() ORDER BY reservationID desc";
		try {
			statement = connection.createStatement();
			rs = statement.executeQuery(sql);
			while (rs.next()) {
				Reservation r = getReservationFromRs(rs);
				reservationsList.add(r);
			}
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		} finally {

			try {
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		return reservationsList;
	}

	public static List<Reservation> getReservations(int customerID) {
		List<Reservation> reservations = new ArrayList<Reservation>();
		try {
			connection = MysqlConnection.getConnection();
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		Statement statement = null;
		String sql = "SELECT * FROM `fkou`.reservation WHERE customerID="
				+ customerID + " ORDER BY reservationID desc";
		try {
			statement = connection.createStatement();
			rs = statement.executeQuery(sql);
			while (rs.next()) {
				Reservation r = getReservationFromRs(rs);
				reservations.add(r);
			}
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		} finally {

			try {
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		return reservations;
	}

	public static List<Reservation> getAllReservations() {
		List<Reservation> reservations = new ArrayList<Reservation>();
		try {
			connection = MysqlConnection.getConnection();
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		Statement statement = null;
		String sql = "SELECT * FROM `fkou`.reservation ORDER BY reservationID asc";
		try {
			statement = connection.createStatement();
			rs = statement.executeQuery(sql);
			while (rs.next()) {
				Reservation r = getReservationFromRs(rs);
				reservations.add(r);
			}
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		} finally {

			try {
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		return reservations;
	}

	public static List<Reservation> getReservationsForTable(int table) {
		List<Reservation> reservations = new ArrayList<Reservation>();
		try {
			connection = MysqlConnection.getConnection();
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		Statement statement = null;
		String sql = "SELECT * FROM `fkou`.reservation WHERE tableID=" + table
				+ " ORDER BY reservationID asc";
		try {
			statement = connection.createStatement();
			rs = statement.executeQuery(sql);
			while (rs.next()) {
				Reservation r = getReservationFromRs(rs);
				reservations.add(r);
			}
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		} finally {

			try {
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		return reservations;
	}

	public static List<Reservation> getReservationsForTableBetweenTime(String fromTime,String toTime,int table) {
		List<Reservation> reservations = new ArrayList<Reservation>();
		try {
			connection = MysqlConnection.getConnection();
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		Statement statement = null;
		String sql = "SELECT * FROM `fkou`.reservation WHERE tableID=" + table
				+ " AND `from`>='" + fromTime + "' AND `to`<='"+toTime+"' ORDER BY reservationID asc";
		//System.out.println(sql);
		try {
			statement = connection.createStatement();
			rs = statement.executeQuery(sql);
			while (rs.next()) {
				Reservation r = getReservationFromRs(rs);
				reservations.add(r);
			}
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		} finally {

			try {
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		return reservations;
	}
	
	
	public static List<Integer> getCurrentOccupiedTableIndex(String t) {
		List<Integer> tables = new ArrayList<Integer>();
		try {
			connection = MysqlConnection.getConnection();
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		Statement statement = null;
		String sql = "SELECT tableID FROM `fkou`.reservation WHERE `from`<='"
				+ t + "' AND `to`>='" + t + "' ";
		try {
			statement = connection.createStatement();
			rs = statement.executeQuery(sql);
			while (rs.next()) {
				int table = rs.getInt("tableID");
				tables.add(table);
			}
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		} finally {

			try {
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return tables;
	}
	public static List<Integer> getOccupiedTableIndex(String fromTime,String toTime) {
		List<Integer> tables = new ArrayList<Integer>();
		try {
			connection = MysqlConnection.getConnection();
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		Statement statement = null;
		String sql = "SELECT tableID FROM `fkou`.reservation WHERE `from`>='"
				+ fromTime + "' AND `to`<='" + toTime + "' ";
		//System.out.println(sql);
		try {
			statement = connection.createStatement();
			rs = statement.executeQuery(sql);
			while (rs.next()) {
				int table = rs.getInt("tableID");
				tables.add(table);
			}
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		} finally {

			try {
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return tables;
	}
	private static Reservation getReservationFromRs(ResultSet rs) {
		Reservation r = null;
		try {
			r = new Reservation(rs.getInt("reservationID"),
					rs.getTimestamp("from"), rs.getTimestamp("to"),
					rs.getInt("numberOfPeople"), rs.getInt("tableID"),
					rs.getInt("tableID"));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return r;
	}

}
